﻿-- =========================================================================================================
-- Author:		MICHAEL RITACCO
-- Copyright:	2010 MEKHANO GROUP LLC
-- Version:		08/21/2010 1.0
-- =========================================================================================================
CREATE PROCEDURE [flcr].[api_device_sel]
	@DEVICE_ID INT = NULL
	,@AUDIT_USER VARCHAR(100) = NULL 
	,@AUDIT_DTTM DATETIME = NULL
	,@AUDIT_DTTM_TZ INT = NULL
	,@AUDIT_MOD_USER VARCHAR(100) = NULL
	,@AUDIT_MOD_DTTM DATETIME = NULL
	,@AUDIT_MOD_DTTM_TZ INT = NULL
	,@WRKFL_ITEM_ID INT = NULL
	,@WRKFL_STATUS_ID INT = NULL
	,@ACTIVE_FLG BIT = NULL
	,@DEVICE_ASSET_NAME VARCHAR(100) = NULL
	,@DEVICE_SERIAL_NUM VARCHAR(100) = NULL
	,@DEVICE_ASSET_TAG VARCHAR(100) = NULL
	,@DEVICE_TYPE INT = NULL
	,@DEVICE_PLATFORM_ID INT = NULL
	,@DOMAIN_ID INT = NULL
	,@ORG_ID INT = NULL
	,@RESOURCE_ID INT = NULL
	,@SLA_ID INT = NULL
	,@FACILITY_ID INT = NULL
	,@ENV_ID INT = NULL
	
AS
BEGIN

	SELECT	 A.[DEVICE_ID]
			,A.[AUDIT_USER]
			,A.[AUDIT_DTTM]
			,A.[AUDIT_DTTM_TZ] 
			,A.[AUDIT_MOD_USER]
			,A.[AUDIT_MOD_DTTM]
			,A.[AUDIT_MOD_DTTM_TZ] 
			,A.[RLS_MASK]
			,A.[RLS_OWNER]
			,A.[WRKFL_STATUS_ID]
			,A.[ACTIVE_FLG]
			,A.[DEVICE_ASSET_NAME]
			,A.[DEVICE_SERIAL_NUM]
			,A.[DEVICE_ASSET_TAG]
			,A.[PARENT_ID]
			,A.[DEVICE_TYPE]
			,B.[DEVICE_TYPE_NAME]
			,A.[DEVICE_PLATFORM_ID]
			,A.[DOMAIN_ID]
			,C.[DOMAIN_NAME]
			,A.[ORG_ID]
			,A.[RESOURCE_ID]
			,A.[SLA_ID]
			,A.[FACILITY_ID]
			,A.[ENV_ID]
	  FROM	[flcr].[DEVICE] A
	 INNER JOIN [flcr].[DEVICE_TYPE] B
		ON	A.[DEVICE_TYPE] = B.[DEVICE_TYPE]
	 INNER JOIN [flcr].[DOMAIN] C
		ON	A.[DOMAIN_ID] = C.[DOMAIN_ID]
	 WHERE	A.[DEVICE_ID] = COALESCE(@DEVICE_ID, A.[DEVICE_ID])
	   AND	A.[AUDIT_USER] = COALESCE(@AUDIT_USER, A.[AUDIT_USER])
	   AND	A.[AUDIT_DTTM] = COALESCE(@AUDIT_DTTM, A.[AUDIT_DTTM])
	   AND	A.[AUDIT_DTTM_TZ] = COALESCE(@AUDIT_DTTM_TZ, A.[AUDIT_DTTM_TZ])
	   AND	A.[AUDIT_MOD_USER] = COALESCE(@AUDIT_MOD_USER, A.[AUDIT_MOD_USER])
	   AND	A.[AUDIT_MOD_DTTM] = COALESCE(@AUDIT_MOD_DTTM, A.[AUDIT_MOD_DTTM])
	   AND	A.[AUDIT_MOD_DTTM_TZ] = COALESCE(@AUDIT_MOD_DTTM_TZ, A.[AUDIT_MOD_DTTM_TZ])
	   AND	A.[WRKFL_ITEM_ID] = COALESCE(@WRKFL_ITEM_ID, A.[WRKFL_ITEM_ID])
	   AND	A.[WRKFL_STATUS_ID] = COALESCE(@WRKFL_STATUS_ID, A.[WRKFL_STATUS_ID])
	   AND	A.[ACTIVE_FLG] = COALESCE(@ACTIVE_FLG, A.[ACTIVE_FLG])
	   AND	A.[DEVICE_ASSET_NAME] = COALESCE(@DEVICE_ASSET_NAME, A.[DEVICE_ASSET_NAME])
	   AND	A.[DEVICE_SERIAL_NUM] = COALESCE(@DEVICE_SERIAL_NUM, A.[DEVICE_SERIAL_NUM])
	   AND	A.[DEVICE_ASSET_TAG] = COALESCE(@DEVICE_ASSET_TAG, A.[DEVICE_ASSET_TAG])
	   AND	A.[DEVICE_TYPE] = COALESCE(@DEVICE_TYPE, A.[DEVICE_TYPE])
	   AND	A.[DEVICE_PLATFORM_ID] = COALESCE(@DEVICE_PLATFORM_ID, A.[DEVICE_PLATFORM_ID])
	   AND	A.[DOMAIN_ID] = COALESCE(@DOMAIN_ID, A.[DOMAIN_ID])
	   AND	A.[ORG_ID] = COALESCE(@ORG_ID, A.[ORG_ID])
	   AND	A.[RESOURCE_ID] = COALESCE(@RESOURCE_ID, A.[RESOURCE_ID])
	   AND	A.[SLA_ID] = COALESCE(@SLA_ID, A.[SLA_ID])
	   AND	A.[FACILITY_ID] = COALESCE(@FACILITY_ID, A.[FACILITY_ID])
	   AND	A.[ENV_ID] = COALESCE(@SLA_ID, A.[ENV_ID])

END